package deal;

import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class XlsxToJsonConverter {

    public static void main(String[] args) {
        String inFile1="C:\\Users\\咚咚咚豆\\Desktop\\inSZ.xlsx";
        String inFile2="C:\\Users\\咚咚咚豆\\Desktop\\outSZ.xlsx";
        String outFile1="test/inSZ.json";
        String outFile2="test/outSZ.json";
        xlsxToJson(inFile1,outFile1);
        xlsxToJson(inFile2,outFile2);
        System.out.println("转换成功");
    }

    public static void xlsxToJson(String InfileName,String OutfileName){
        String inputXlsxFile = InfileName;
        String outputJsonFile = OutfileName;

        try {
            List<Map<String, String>> data = readExcel(inputXlsxFile);
            printJson(data, outputJsonFile);
        } catch (IOException | ParseException e) {
            e.printStackTrace();
        }
    }

    private static List<Map<String, String>> readExcel(String filePath) throws IOException, ParseException {
        List<Map<String, String>> data = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            Iterator<Row> rowIterator = sheet.iterator();
            // 跳过第一行（列名）
            if (rowIterator.hasNext()) {
                rowIterator.next();
            }

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Map<String, String> rowData = convertRowToMap(row);
                data.add(rowData);
            }
        }

        return data;
    }

    private static Map<String, String> convertRowToMap(Row row) throws ParseException {
        Map<String, String> rowData = new HashMap<>();
        rowData.put("XH", String.valueOf((int) row.getCell(0).getNumericCellValue()));
        rowData.put("CP", getCellValueAsString(row.getCell(1)));
        rowData.put("CX", getCellValueAsString(row.getCell(2)));
        rowData.put("RKSJ", formatCellDate(row.getCell(3)));
        rowData.put("SFZRKMC", getCellValueAsString(row.getCell(4)));
        rowData.put("CKSJ", formatCellDate(row.getCell(5)));
        rowData.put("SFZCKMC", getCellValueAsString(row.getCell(6)));
        rowData.put("BZ", getCellValueAsString(row.getCell(7)));

        return rowData;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(date);
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            default:
                return null;
        }
    }

    private static String formatCellDate(Cell cell) {
        if (cell == null) {
            return null;
        }

        if (cell.getCellType() == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                return new SimpleDateFormat("yyyy-MM-dd HH:mm").format(date);
            }
        } else if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        }

        return null;
    }


    private static void printJson(List<Map<String, String>> data, String outputJsonFile) {
        ObjectMapper objectMapper = new ObjectMapper();

        try (FileWriter jsonWriter = new FileWriter(outputJsonFile)) {
            for (Map<String, String> rowData : data) {
                jsonWriter.write(objectMapper.writeValueAsString(rowData));
                jsonWriter.write(System.lineSeparator());
            }

            System.out.println("JSON 文件输出成功！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
